﻿CREATE PROCEDURE [dbo].[proc_Finance_Record_Getlist]
	(
		@CompanyId int,
		@StartDate nchar(10),
		@EndDate nchar(10)
	)
AS
Begin
	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY Id)AS Row
	,Id As CompanyId
	,CompanyName
	,ISNULL((Select SYXJ From Finance_Record Where CompanyId=Company.Id and Convert(nchar(10),CreateDate,120)=@EndDate),0) AS SYXJ --实有现金
	,ISNULL((Select SYYH From Finance_Record Where CompanyId=Company.Id and Convert(nchar(10),CreateDate,120)=@EndDate),0) AS SYYH --实有银行
	,ISNULL((Select YYXJ From Finance_Record Where CompanyId=Company.Id and Convert(nchar(10),CreateDate,120)=@EndDate),0) AS YYXJ --应有现金
	,ISNULL((Select YYYH From Finance_Record Where CompanyId=Company.Id and Convert(nchar(10),CreateDate,120)=@EndDate),0) AS YYYH --应有银行
	,ISNULL((Select ZHYE From Finance_Record Where CompanyId=Company.Id and Convert(nchar(10),CreateDate,120)=@EndDate),0) AS ZHYE --账户余额
	,ISNULL((Select Sum(JKHJ) From Finance_Record Where CompanyId=Company.Id and Convert(nchar(10),CreateDate,120) between @StartDate and @EndDate),0) AS JKHJ --借款合计
	,ISNULL((Select ZJHJ From Finance_Record Where CompanyId=Company.Id and Convert(nchar(10),CreateDate,120)=@EndDate),0) AS ZJHJ --资金合计
	,ISNULL((Select Sum(YYXJPZ) From Finance_Record Where CompanyId=Company.Id and Convert(nchar(10),CreateDate,120) between @StartDate and @EndDate),0) AS YYXJPZ --应有现金平整
	,ISNULL((Select Sum(YYYHPZ) From Finance_Record Where CompanyId=Company.Id and Convert(nchar(10),CreateDate,120) between @StartDate and @EndDate),0) AS YYYHPZ --应有银行平整
	,ISNULL((Select Sum(CE) From Finance_Record Where CompanyId=Company.Id and Convert(nchar(10),CreateDate,120) between @StartDate and @EndDate),0) AS CE --差额
	,ISNULL((Select Sum(YSHJ) From Finance_Record Where CompanyId=Company.Id and Convert(nchar(10),CreateDate,120) between @StartDate and @EndDate),0) AS YSHJ --应收合计
	,ISNULL((Select Sum(YFHJ) From Finance_Record Where CompanyId=Company.Id and Convert(nchar(10),CreateDate,120) between @StartDate and @EndDate),0) AS YFHJ --应付合计
	,ISNULL((Select Sum(XZML) From Finance_Record Where CompanyId=Company.Id and Convert(nchar(10),CreateDate,120) between @StartDate and @EndDate),0) AS XZML --新增毛利
	,ISNULL((Select Sum(XFML) From Finance_Record Where CompanyId=Company.Id and Convert(nchar(10),CreateDate,120) between @StartDate and @EndDate),0) AS XFML --续费毛利
	,ISNULL((Select Sum(XZYS) From Finance_Record Where CompanyId=Company.Id and Convert(nchar(10),CreateDate,120) between @StartDate and @EndDate),0) AS XZYS --新增应收
	,ISNULL((Select Sum(XZYF) From Finance_Record Where CompanyId=Company.Id and Convert(nchar(10),CreateDate,120) between @StartDate and @EndDate),0) AS XZYF --新增应付
	,ISNULL((Select Sum(CDJE) From Finance_Record Where CompanyId=Company.Id and Convert(nchar(10),CreateDate,120) between @StartDate and @EndDate),0) AS CDJE --成单金额
	,ISNULL((Select Sum(WKSR) From Finance_Record Where CompanyId=Company.Id and Convert(nchar(10),CreateDate,120) between @StartDate and @EndDate),0) AS WKSR --尾款收入
	,ISNULL((Select Sum(FYLX) From Finance_Record Where CompanyId=Company.Id and Convert(nchar(10),CreateDate,120) between @StartDate and @EndDate),0) AS FYLX --非营利性
	From Company Where (@CompanyId=0 Or Id=@Companyid)
	)
	Select * From list
End
	RETURN
